# Query Builder

Retrieving a Single Object

find()

The find method retrieve the object corresponding to the specified primary key that matches any supplied options

customer = Customer.find(10)
#<Customer id: 10, first_name: "Ryan">

1
2

take()

The take method retrieves a record without any implicit ordering.

customer = Customer.take
#<Customer id: 1, first_name: "Lifo">

customers = Customer.take(2)
[#<Customer id: 1, first_name: "Lifo">, #<Customer id: 220, first_name: "Sara">]

1
2
3
4
5

first()

The first method finds the first record ordered by primary key (default).

customer = Customer.first
#<Customer id: 1, first_name: "Lifo">

customers = Customer.first(3)
[#<Customer id: 1, first_name: "Lifo">, #<Customer id: 2, first_name: "Fifo">, #<Customer id: 3, first_name: "Filo">]

1
2
3
4
5

last()

The last method finds the last record ordered by primary key (default).

customer = Customer.last
#<Customer id: 221, first_name: "Russel">

customers = Customer.last(3)
[#<Customer id: 219, first_name: "James">, #<Customer id: 220, first_name: "Sara">, #<Customer id: 221, first_name: "Russel">]

customer = Customer.order(:first_name).last
#<Customer id: 220, first_name: "Sara">

1
2
3
4
5
6
7
8

find_by()

The find_by method finds the first record matching some conditions.

Customer.find_by first_name: 'Lifo'
#<Customer id: 1, first_name: "Lifo">

1
2
3

Retrieving Multiple Objects in Batches

find_each()

The find_each method retrieves records in batches and then yields each one to the block.

Customer.find_each do |customer|
  NewsMailer.weekly(customer).deliver_now
end

Customer.where(weekly_subscriber: true).find_each do |customer|
  NewsMailer.weekly(customer).deliver_now
end

#batch size option allows you to specify the number of records to be retrieved in each batch
Customer.find_each(batch_size: 5000) do |customer|
  NewsMailer.weekly(customer).deliver_now
end

#start option allows you to configure the first ID of the sequence whenever the lowest ID is not the one you need.

Customer.find_each(start: 2000) do |customer|
  NewsMailer.weekly(customer).deliver_now
end

#finish allows you to configure the last ID of the sequence whenever the highest ID is not the one you need.

Customer.find_each(start: 2000, finish: 10000) do |customer|
  NewsMailer.weekly(customer).deliver_now
end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

find_in_batches()

The find_in_batches method is similar to find_each, since both retrieve batches of records. The difference is that find_in_batchesyields batches to the block as an array of models, instead of individually.

# Give add_customers an array of 1000 customers at a time.
Customer.find_in_batches do |customers|
  export.add_customers(customers)
end

# Give add_customers an array of 1000 recently active customers at a time.
Customer.recently_active.find_in_batches do |customers|
  export.add_customers(customers)
end

#batch_size
Customer.find_in_batches(batch_size: 2500) do |customers|
  export.add_customers(customers)
end

#start
Customer.find_in_batches(batch_size: 2500, start: 5000) do |customers|
  export.add_customers(customers)
end

#finish
Customer.find_in_batches(finish: 7000) do |customers|
  export.add_customers(customers)
end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Conditions

The [where](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/QueryMethods.html#method-i-where) method allows you to specify conditions to limit the records returned, representing the WHERE-part of the SQL statement. Conditions can either be specified as a string, array, or hash.

#Pure String Conditions
Book.where("title = 'Introduction to Algorithms'")

#Array Conditions
Book.where("title = ?", params[:title])

Book.where("title = ? AND out_of_print = ?", params[:title], false)

Book.where("title LIKE ?",
  Book.sanitize_sql_like(params[:title]) + "%")

#Hash Conditions
Book.where(out_of_print: true)

Book.where(created_at: (Time.now.midnight - 1.day)..Time.now.midnight)

Customer.where(orders_count: [1,3,5])

#NOT Condition
Customer.where.not(orders_count: [1,3,5])

#OR Condition
Customer.where(last_name: 'Smith').or(Customer.where(orders_count: [1,3,5]))

#AND Condition
Customer.where(last_name: 'Smith').where(orders_count: [1,3,5]))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

Ordering

To retrieve records from the database in a specific order, you can use the [order](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/QueryMethods.html#method-i-order) method.

Book.order(:created_at)
# OR
Book.order("created_at")

Book.order(created_at: :desc)
# OR
Book.order(created_at: :asc)
# OR
Book.order("created_at DESC")
# OR
Book.order("created_at ASC")

Book.order(title: :asc, created_at: :desc)
# OR
Book.order(:title, created_at: :desc)
# OR
Book.order("title ASC, created_at DESC")
# OR
Book.order("title ASC", "created_at DESC")

Book.order("title ASC").order("created_at DESC")

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

Select Statement

You may not always want to select all columns from a database table. Using the select method, you can specify a custom "select" clause for the query:
```
Book.select(:isbn, :out_of_print)
# OR
Book.select("isbn, out_of_print")
```
1
2
3
4
If you would like to only grab a single record per unique value in a certain field, you can use [distinct](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/QueryMethods.html#method-i-distinct):
```
Customer.select(:last_name).distinct
```
1
Limit and Offset

You can use limitto specify the number of records to be retrieved, and use offset to specify the number of records to skip before starting to return the records. For example
```
Customer.limit(5)
```
1
Adding offset to that
```
Customer.limit(5).offset(30)
```
1
Group

To apply a GROUP BY clause to the SQL fired by the finder, you can use the [group](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/QueryMethods.html#method-i-group) method.

For example, if you want to find a collection of the dates on which orders were created:
```
Order.select("created_at").group("created_at")
```
1
To get the total of grouped items on a single query, call [count](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Calculations.html#method-i-count) after the group.
```
irb> Order.group(:status).count
=> {"being_packed"=>7, "shipped"=>12}
```
1
2
Having

SQL uses the HAVING clause to specify conditions on the GROUP BY fields. You can add the HAVING clause to the SQL fired by the Model.find by adding the [having](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/QueryMethods.html#method-i-having) method to the find.

For example:
```
Order.select("created_at, sum(total) as total_price").
  group("created_at").having("sum(total) > ?", 200)
```
1
2

Joining Table

Active Record provides two finder methods for specifying JOIN clauses on the resulting SQL: joinsand left_outer_joins. While joinsshould be used for INNER JOINor custom queries, left_outer_joins is used for queries using LEFT OUTER JOIN

#Using String
Author.joins("INNER JOIN books ON books.author_id = authors.id AND books.out_of_print = FALSE")

#Using Array/Hash of Named Associations
Book.joins(:reviews)

#Using Multiple Association
Book.joins(:author, :reviews)

#Joining Nested Associations (Single Level)
Book.joins(reviews: :customer)

#Joining Nested Associations (Multiple Level)

Author.joins(books: [{ reviews: { customer: :orders } }, :supplier] )

#Specifying Conditions on the Joined Tables
time_range = (Time.now.midnight - 1.day)..Time.now.midnight
Customer.joins(:orders).where('orders.created_at' => time_range).distinct

#or

time_range = (Time.now.midnight - 1.day)..Time.now.midnight
Customer.joins(:orders).where(orders: { created_at: time_range }).distinct

#left joins
Customer.left_outer_joins(:reviews).distinct.select('customers.*, COUNT(reviews.*) AS reviews_count').group('customers.id')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

Eager Loading Relations

preload

Preload loads the association data in a separate query. ``

User.preload(:posts).to_a

# =>
SELECT "users".* FROM "users"
SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (1)

1
2
3
4
5

This is how includes loads data in the default case.

Since preload always generates two sql we can't use posts table in where condition. Following query will result in an error.

User.preload(:posts).where("posts.desc='ruby is awesome'")

# =>
SQLite3::SQLException: no such column: posts.desc:
SELECT "users".* FROM "users"  WHERE (posts.desc='ruby is awesome')

1
2
3
4
5

With preload where clauses can be applied.

User.preload(:posts).where("users.name='Neeraj'")

# =>
SELECT "users".* FROM "users"  WHERE (users.name='Neeraj')
SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (3)

1
2
3
4
5

includes

Includes loads the association data in a separate query just like preload.

However it is smarter than preload. Above we saw that preload failed for query User.preload(:posts).where("posts.desc='ruby is awesome'"). Let's try same with includes.

User.includes(:posts).where('posts.desc = "ruby is awesome"').to_a

# =>
SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
       "posts"."title" AS t1_r1,
       "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"
WHERE (posts.desc = "ruby is awesome")

1
2
3
4
5
6
7
8

As you can see includes switches from using two separate queries to creating a single LEFT OUTER JOIN to get the data. And it also applied the supplied condition.

So includes changes from two queries to a single query in some cases. By default for a simple case it will use two queries. Let's say that for some reason you want to force a simple includescase to use a single query instead of two. Use references to achieve that.

eager_load

eager loading loads all association in a single query using LEFT OUTER JOIN .

User.eager_load(:posts).to_a

# =>
SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
       "posts"."title" AS t1_r1, "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"

1
2
3
4
5
6

This is exactly what includes does when it is forced to make a single query when where or order clause is using an attribute from posts table.

Scopes

Scoping allows you to specify commonly-used queries which can be referenced as method calls on the association objects or models.

To define a simple scope, we use the [scope](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Scoping/Named/ClassMethods.html#method-i-scope) method inside the class, passing the query that we'd like to run when this scope is called:

class Book < ApplicationRecord
  scope :out_of_print, -> { where(out_of_print: true) }
	#chainble scope
	scope :out_of_print_and_expensive, -> { out_of_print.where("price > 500") }

	#passing arguments
	scope :costs_more_than, ->(amount) { where("price > ?", amount) }
	#book.costs_more_than(500)

	#conditions
	scope :created_before, ->(time) { where("created_at < ?", time) if time.present? }

	#default scope
	default_scope { where(out_of_print: false) }
end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Dynamic Filters

For every field (also known as an attribute) you define in your table, Active Record provides a finder method. If you have a field called first_name on your Customer model for example, you get the instance method find_by_first_name for free from Active Record. If you also have a lockedfield on the Customer model, you also get find_by_locked method.

You can specify an exclamation point (!) on the end of the dynamic finders to get them to raise an ActiveRecord::RecordNotFound error if they do not return any records, like Customer.find_by_first_name!("Ryan")

If you want to find both by first_name and orders_count, you can chain these finders together by simply typing "and" between the fields. For example, Customer.find_by_first_name_and_orders_count("Ryan", 5)
Enums

An enum lets you define an Array of values for an attribute and refer to them by name. The actual value stored in the database is an integer that has been mapped to one of the values.

Declaring an enum will:
- Create scopes that can be used to find all objects that have or do not have one of the enum values
- Create an instance method that can be used to determine if an object has a particular value for the enum
- Create an instance method that can be used to change the enum value of an object
for all possible values of an enum.

For example, given this [enum](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Enum.html#method-i-enum) declaration:
```
class Order < ApplicationRecord
  enum :status, [:shipped, :being_packaged, :complete, :cancelled]
end
```
1
2
3
These scopes (opens new window) are created automatically and can be used to find all objects with or without a particular value for status:
```
irb> Order.shipped
=> #<ActiveRecord::Relation> # all orders with status == :shipped
irb> Order.not_shipped
=> #<ActiveRecord::Relation> # all orders with status != :shipped
```
1
2
3
4
These instance methods are created automatically and query whether the model has that value for the status enum:
```
irb> order = Order.shipped.first
irb> order.shipped?
=> true
irb> order.complete?
=> false
```
1
2
3
4
5
These instance methods are created automatically and will first update the value of status to the named value and then query whether or not the status has been successfully set to the value:
```
irb> order = Order.first
irb> order.shipped!
UPDATE "orders" SET "status" = ?, "updated_at" = ? WHERE "orders"."id" = ?  [["status", 0], ["updated_at", "2019-01-24 07:13:08.524320"], ["id", 1]]
=> true
```
1
2
3
4

Methods Chaining

#Retrieving filtered data from multiple tables
Customer
  .select('customers.id, customers.last_name, reviews.body')
  .joins(:reviews)
  .where('reviews.created_at > ?', 1.week.ago)

#Retrieving specific data from multiple tables
Book
  .select('books.id, books.title, authors.first_name')
  .joins(:author)
  .find_by(title: 'Abstraction and Specification in Program Development')

1
2
3
4
5
6
7
8
9
10
11

Find or build New Objects

It's common that you need to find a record or create it if it doesn't exist. You can do that with the find_or_create_by and find_or_create_by!methods.

find_or_create_by

The [find_or_create_by](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Relation.html#method-i-find_or_create_by) method checks whether a record with the specified attributes exists. If it doesn't, then create is called. Let's see an example.
```
Customer.find_or_create_by(first_name: 'Andy')
```
1
find_or_create_by!

You can also use [find_or_create_by!](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Relation.html#method-i-find_or_create_by-21)to raise an exception if the new record is invalid.

find_or_initialize_by

The [find_or_initialize_by](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Relation.html#method-i-find_or_initialize_by)method will work just like find_or_create_by but it will call new instead of create.

Find by SQL

find_by_sql

Customer.find_by_sql("SELECT * FROM customers INNER JOIN orders ON customers.id = orders.customer_id ORDER BY customers.created_at desc")

select_all

select_allwill retrieve objects from the database using custom SQL just like find_by_sql but will not instantiate them. This method will return an instance of ActiveRecord::Result class and calling to_aon this object would return you an array of hashes where each hash indicates a record.

irb> Customer.connection.select_all("SELECT first_name, created_at FROM customers WHERE id = '1'").to_a
=> [{"first_name"=>"Rafael", "created_at"=>"2012-11-10 23:23:45.281189"}, 
{"first_name"=>"Eileen", "created_at"=>"2013-12-09 11:22:35.221282"}]

1
2
3

pluck

[pluck](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Calculations.html#method-i-pluck)can be used to query single or multiple columns from the underlying table of a model. It accepts a list of column names as an argument and returns an array of values of the specified columns with the corresponding data type.

irb> Book.where(out_of_print: true).pluck(:id)
SELECT id FROM books WHERE out_of_print = true
=> [1, 2, 3]

irb> Order.distinct.pluck(:status)
SELECT DISTINCT status FROM orders
=> ["shipped", "being_packed", "cancelled"]

irb> Customer.pluck(:id, :first_name)
SELECT customers.id, customers.first_name FROM customers
=> [[1, "David"], [2, "Fran"], [3, "Jose"]]

1
2
3
4
5
6
7
8
9
10
11

ids

[ids](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Calculations.html#method-i-ids) can be used to pluck all the IDs for the relation using the table's primary key.

irb> Customer.ids
SELECT id FROM customers

1
2

Existence of Objects

If you simply want to check for the existence of the object there's a method called [exists?](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/FinderMethods.html#method-i-exists-3F). This method will query the database using the same query as find, but instead of returning an object or collection of objects it will return either trueor false.
```
Customer.exists?(1)

Customer.exists?(id: [1,2,3])
# or
Customer.exists?(first_name: ['Jane', 'Sergei'])

Customer.where(first_name: 'Ryan').exists?
```
1
2
3
4
5
6
7

Calculations

#count
Customer.count

#average
Order.average("subtotal")

#minimum
Order.minimum("subtotal")

#maximum
Order.maximum("subtotal")

#sum
Order.sum("subtotal")

1
2
3
4
5
6
7
8
9
10
11
12
13
14

← Callbacks Digging Deeper →